Spreadsheets in modules
Spreadsheets are invoked in module logic using the ExecuteSpreadsheet function. The single parameter is the Name value as defined in the Spreadsheets tab.
A spreadsheet can be called in any module. Multiple spreadsheets can be called in the same module, or a single spreadsheet can be called multiple times.
There is no restriction about where and how spreadsheets can be invoked, but normally a spreadsheet is executed once in the last time period, so that time series variables will have all of their values available. Hence it is typical to use the logic condition 'IsLastPeriod() = 1'. For example:
For version 16.5 Update 25 and later: Multiple linked spreadsheets can be configured to be executed using a single call of the ExecuteSpreadsheet function. A spreadsheet 'group' is set up in the Spreadsheets configuration tab, and then the group name is used as the input to ExecuteSpreadsheet. See Spreadsheets for more information.
For version 16.5 Update 31 and later: The ExecuteSpreadsheet function has two added optional parameters to control the saving of calculated spreadsheets in Result Set calculations (see Standard result set): the Boolean 'isSavingWorkingFile' (default 'true' if the parameter is not included) determines if data will be saved for each function call (only when the Result Set Preserve Calculated Spreadsheets option is enabled); the text parameter 'workingFileSavingPath' is an optional file path to specify the folder where the saved files will be stored (and an index file will not be generated); the default behavior is that the default path will be used and an index file will be generated.
Working with temporary variables
Temporary variables can be populated using standard functions before being used as inputs to the spreadsheet model. For example, see how '@WI' is handled in the screenshots below:
Sometimes it is necessary to run all of the time steps to retrieve temporary variables and execute the spreadsheet in the last period. The following logic example shows the use of the conditional block 'If IsLastPeriod() <> 1' to split the procedure into two parts:
Working with calculation phases
The calculation logic can be complex to understand which inputs are available to the spreadsheet at which points: the key principle is that the outer loop within a phase is the time loop not the partner loop, so it may be the case that additional phases need to be added to the regime. (See Calculation Phases.)
In the example below the calculation of the Gross Abandonment happens in a previous and separate phase than the calculation of the Abandonment for the different net partners:
